package org.yonggan.shop.sql.report

import org.apache.spark.sql.DataFrame
import org.yonggan.shop.constant.ConfigurationManager
import org.yonggan.shop.utils.{SQLUtils, SparkUtils}

/**
  * 统计商品大分类
  * 各个成交商品总金额的大类别排名与全部排名
  */
object RptCategoryData {

  def main(args: Array[String]): Unit = {

    val spark = SparkUtils.getSparkSession("各个成交商品的大类别排名与全部排名")
    val sc = spark.sparkContext
    sc.setLogLevel("WARN")

    // 文件输出
    val jsonDF: DataFrame = spark.read.json(ConfigurationManager.TASK_INJSON)
    jsonDF.createTempView("v_order_goods_info")

    // 过滤合法商品
    val filterSQL =
      """
        |SELECT goods.cid cid , goods.pid pid,  SUM(goods.money) sm
        |FROM
        |(SELECT EXPLODE (goods) goods FROM v_order_goods_info)
        |GROUP BY cid ,pid
      """.stripMargin

      SQLUtils.executor(filterSQL,spark).createTempView("v_order_goods")
      // 统计各个分类信息

    val groupedRank =
      """
        |SELECT cid, pid, sm,
        |RANK() OVER(PARTITION BY cid ORDER BY sm DESC) crk,
        |RANK() OVER(ORDER BY sm DESC) grk
        |FROM v_order_goods
      """.stripMargin

     //统计指标
    SQLUtils.executor(groupedRank,spark).show()

//    +---+--------------------+------+---+---+
//    |cid|                 pid|    sm|crk|grk|
//    +---+--------------------+------+---+---+
//    |  1|5ace2f8157f58a368...| 28000|  1|  3|
//    |  1|5ace32e357f58a368...| 19000|  2|  4|
//    |  1|5ace323c57f58a368...| 11000|  3|  6|
//    |  4|5ace2f3c57f58a368...|168000|  1|  1|
//    |  4|5ad16c9a57f58a330...| 45000|  2|  2|
//    |  4|5ace2d7057f58a368...| 14000|  3|  5|
//    +---+--------------------+------+---+---+

    spark.stop
  }
}
